package com.red.wood.parser;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Ignore;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import com.red.wood.base.BaseTest;
import com.red.wood.common.ip.IPAddressUtils;
import com.red.wood.common.springjdbc.CommonDao;
import com.red.wood.common.util.CommonUtils;

public class ProvinceParse extends BaseTest{

	private static final Log log = LogFactory.getLog(ProvinceParse.class);
	
	@Autowired
	private CommonDao dao;
	
	@Autowired
	//@Qualifier("jdbcTemplateOracle")
	private JdbcTemplate jdbcTemplate;
	
	@Test
	@Ignore
	public void cdnDataParse() {
		StringBuilder sqlSb = new StringBuilder("select cdnId,cdnName,cdnAdd,corp,timstamp from t_cdn_cdndata where 1 = 1");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String cdnAdd = CommonUtils.valueOf(map.get("cdnAdd"));
		
			String province = C3Parse.addressAdscription(cdnAdd);
			map.put("province", province);
		}
		
		dao.deleteTableData("delete from t_cdn_cdndata");
		String sql = "insert into t_cdn_cdndata(province,cdnId,cdnName,cdnAdd,corp,timstamp) values (?,?,?,?,?,?)";
		String columns = "province,cdnId,cdnName,cdnAdd,corp,timstamp";
		dao.insertBatchTableData(list, sql, columns,5000);
	}
	
	@Test
	@Ignore
	public void cdnCustomerParse() {
		StringBuilder sqlSb = new StringBuilder("select cdnId,customerId,unitName,unitNature,idType,idNumber,address,timstamp from t_cdn_customerinfo");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String address = CommonUtils.valueOf(map.get("address"));
			
			String province = C3Parse.addressAdscription(address);
			map.put("province", province);
		}
		
		dao.deleteTableData("delete from t_cdn_customerinfo");
		String sql = "insert into t_cdn_customerinfo(province,cdnId,customerId,unitName,unitNature,idType,idNumber,address,timstamp) values (?,?,?,?,?,?,?,?,?)";
		String columns = "province,cdnId,customerId,unitName,unitNature,idType,idNumber,address,timstamp";
		dao.insertBatchTableData(list, sql, columns,10000);
	}
	
	@Test
	@Ignore
	public void cdnIpParse() throws Exception {
		StringBuilder sqlSb = new StringBuilder("select cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime from t_cdn_cdnnet_node_house_ipseg where 1 = 1");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String startIp = CommonUtils.valueOf(map.get("startIp"));
		
			String[] area = ReadIPFile.getInstance().find(startIp);
			
			map.put("province", area[1]);
		}
		
		dao.deleteTableData("delete from t_cdn_cdnnet_node_house_ipseg");
		String sql = "insert into t_cdn_cdnnet_node_house_ipseg(province,cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "province,cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime";
		dao.insertBatchTableData(list, sql, columns,10000);
	}
	
	@Test
	@Ignore
	public void cdnDomainParse() throws Exception {
		/*StringBuilder sqlSb = new StringBuilder("select cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime from t_cdn_cdnnet_node_house_ipseg where 1 = 1");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String startIp = CommonUtils.valueOf(map.get("startIp"));
		
			String[] area = ReadIPFile.getInstance().find(startIp);
			
			map.put("province", area[1]);
		}
		
		dao.deleteTableData("delete from t_cdn_cdnnet_node_house_ipseg");
		String sql = "insert into t_cdn_cdnnet_node_house_ipseg(province,cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "province,cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime";
		dao.insertBatchTableData(list, sql, columns,10000);*/
		String key = "a";
		System.err.println(getProvinceMap(key));
	}
	
	@Test
	@Ignore
	public void ircsDataParse() {
		StringBuilder sqlSb = new StringBuilder("select ircsId,ircsName,ircsAdd,ircsZip,corp from t_ircs_ircsdata where 1 = 1");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String cdnAdd = CommonUtils.valueOf(map.get("ircsAdd"));
		
			String province = C3Parse.addressAdscription(cdnAdd);
			map.put("province", province);
		}
		
		dao.deleteTableData("delete from t_ircs_ircsdata");
		String sql = "insert into t_ircs_ircsdata(province,ircsId,ircsName,ircsAdd,ircsZip,corp) values (?,?,?,?,?,?)";
		String columns = "province,ircsId,ircsName,ircsAdd,ircsZip,corp";
		dao.insertBatchTableData(list, sql, columns,5000);
	}
	
	@Test
	@Ignore
	public void ircsIpParse() throws Exception {
		
		StringBuilder sqlSb = new StringBuilder("select ircsId,ipInfoId,startIp,endIp,type,sourceUnit,allocationUnit,allocationTime,timstamp from t_ircs_ipinfo where 1 = 1");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String startIp = CommonUtils.valueOf(map.get("startIp"));
		
			String[] area = ReadIPFile.getInstance().find(startIp);
			
			map.put("province", area[1]);
		}
		
		dao.deleteTableData("delete from t_ircs_ipinfo");
		String sql = "insert into t_ircs_ipinfo(province,ircsId,ipInfoId,startIp,endIp,type,sourceUnit,allocationUnit,allocationTime,timstamp) values (?,?,?,?,?,?,?,?,?,?)";
		String columns = "province,ircsId,ipInfoId,startIp,endIp,type,sourceUnit,allocationUnit,allocationTime,timstamp";
		dao.insertBatchTableData(list, sql, columns,5000);
	}
	
	@Test
	@Ignore
	public void ircsUser() {
		//List<Map<String,Object>> list = jdbcTemplate.queryForList("select t.ircsid,t.userid,t.unitname,t.idtype,t.idno,t.unitnature,t.officermobile,t.officertel,t.officeremail,t.address,t.zipcode,t.registertime from t_ods_ircs_queryUserInfo t");
		/*StringBuilder sqlSb = new StringBuilder("select ircsId,userId,unitName,nature,idType,unitName,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp from t_ircs_userinfo_result");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String cdnAdd = CommonUtils.valueOf(map.get("unitadd"));
		
			String province = C3Parse.addressAdscription(cdnAdd);
			map.put("province", province);
		}
		
		dao.deleteTableData("delete from t_ircs_userinfo_result");
		String sql = "insert into t_ircs_userinfo_result(province,ircsId,userId,unitName,nature,idType,unitName,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "province,ircsId,userId,unitName,nature,idType,unitName,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp";
		dao.insertBatchTableData(list, sql, columns,10000);*/

		try {
			int offset = 0;
			int pageSize = 10000;
			int totalCount = 0;
			int i = 0;
			do{
				//由于数据量比较大，一次性无法将所有数据传过来，故做分页处理，每次处理10000条数据
				List<Map<String,Object>> list = getIrcsUserInfo(pageSize, offset);
				for(Map<String,Object> map : list) {
					String cdnAdd = CommonUtils.valueOf(map.get("unitadd"));
				
					String province = C3Parse.addressAdscription(cdnAdd);
					map.put("province", province);
				}
				String sql = "insert into t_ircs_userinfo_result(province,ircsId,userId,nature,idType,unitName,unitNature,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				String columns = "province,ircsId,userId,nature,idType,unitName,unitNature,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp";
				if(i == 0 && list != null && list.size() > 0) {
					dao.deleteTableData("delete from t_ircs_userinfo_result");
				}
				dao.insertBatchTableData(list, sql, columns,10000);
				i++;
				totalCount = list.size();
				offset = offset + pageSize;
			}while(pageSize == totalCount);
		}catch(Exception e) {
			e.printStackTrace();
		}
	
	}
	@Test
	@Ignore
	public void ircsIpGetWayParse() throws Exception {
		
		StringBuilder sqlSb = new StringBuilder("select ircsId,gatewayId,bandWidth,linkType,accessUnit,gatewayIp,internetAccess,timstamp from t_ircs_gatewayinfo where 1 = 1");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		
		for(Map<String,Object> map : list) {
			String gatewayIp = CommonUtils.valueOf(map.get("gatewayIp"));
			try {
				String[] area = ReadIPFile.getInstance().find(gatewayIp);
				map.put("province", area[1]);
			}catch(Exception e) {
				if(log.isErrorEnabled()) {
					log.error(e.getMessage(),e);
				}
				System.err.println(gatewayIp);
				map.put("province","");
			}
		}
		
		dao.deleteTableData("delete from t_ircs_gatewayinfo");
		String sql = "insert into t_ircs_gatewayinfo(province,ircsId,gatewayId,bandWidth,linkType,accessUnit,gatewayIp,internetAccess,timstamp) values (?,?,?,?,?,?,?,?,?)";
		String columns = "province,ircsId,gatewayId,bandWidth,linkType,accessUnit,gatewayIp,internetAccess,timstamp";
		dao.insertBatchTableData(list, sql, columns,10000);
	}
	
	
	public List<Map<String, Object>> getIrcsUserInfo(int pageSize, int offset) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ircsId,userId,unitName,nature,idType,unitNature,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp from t_ircs_userinfo_result1");
		sql.append(" limit "+pageSize +" offset "+offset);
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public String getProvinceMap(String key){
		Map<String,String> map = new HashMap<String,String>();
		map.put("京","北京");	
		map.put("津","天津");	
		map.put("冀","河北");	
		map.put("晋","山西");	
		map.put("蒙","内蒙古");
		map.put("辽","辽宁");	
		map.put("吉","吉林");	
		map.put("黑","黑龙江");
		map.put("沪","上海");	
		map.put("苏","江苏");	
		map.put("浙","浙江");	
		map.put("皖","安徽");	
		map.put("闽","福建");	
		map.put("赣","江西");	
		map.put("鲁","山东");	
		map.put("豫","河南");	
		map.put("鄂","湖北");	
		map.put("湘","湖南");	
		map.put("粤","广东");	
		map.put("桂","广西");	
		map.put("琼","海南");	
		map.put("渝","重庆");	
		map.put("川","四川");	
		map.put("黔","贵州");	
		map.put("滇","云南");	
		map.put("藏","西藏");	
		map.put("陕","陕西");	
		map.put("甘","甘肃");	
		map.put("青","青海");	
		map.put("宁","宁夏");	
		map.put("新","新疆");	
		map.put("台","台湾");	
		map.put("港","香港");	
		map.put("澳","澳门");	
		String province = "";
		if(StringUtils.isNotBlank(key) || key.length() > 1) {
			province = map.get(key.substring(0, 1));
		}
		return StringUtils.isNotBlank(province) ? province : "";
	}
}
